
/*
'===============================================================================
'  HNG.Portal.BL.TblThanhVienRoleManager
'===============================================================================
*/

using System;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using HNG.Portal.BLL;
using HNG.Portal.DAL;
using HNG.Portal.DAL.EntityClasses;
using HNG.Portal.DAL.FactoryClasses;
using HNG.Portal.DAL.HelperClasses;
using SD.LLBLGen.Pro.ORMSupportClasses;

namespace HNG.Portal.BLL
{
	public class TblThanhVienRoleManager : TblThanhVienRoleManagerBase
	{
        SqlConnection Conn = new SqlConnection(HNG.LIB.Common.Connection.strConn);
		/// <summary>
		/// Purpose: Class constructor.
		/// </summary>
		public TblThanhVienRoleManager()
		{
			// Nothing for now.
		}
        
        public DataTable GetRoleByUserName(int SiteMapID)
        {
            SqlParameter[] _SqlParameter = new SqlParameter[1];
            _SqlParameter[0] = new SqlParameter("@TenDangNhap", SiteMapID);
            return AE.SqlHelper.ExecuteDataTable(Conn, "sp_getRoleBySiteMapID", _SqlParameter);
        }

        public DataTable GetSiteMapByThanhVienID(int ThanhVienID)
        {
            SqlParameter[] _SqlParameter = new SqlParameter[1];
            _SqlParameter[0] = new SqlParameter("@ThanhVienID", ThanhVienID);
            return AE.SqlHelper.ExecuteDataTable(Conn, "sp_GetSiteMapByThanhVienID", _SqlParameter);
        }       

        public DataTable be_selectAllThanhVienRole(TblThanhVienRoleEntity _TblThanhVienRoleEntity)
        {
            System.Text.StringBuilder sbSql = new System.Text.StringBuilder();
            SqlParameter[] _SqlParameter = new SqlParameter[3];
            try
            {
                sbSql.Append("WITH tblTemp AS").Append("\n");
                sbSql.Append("(").Append("\n");
                sbSql.Append("SELECT ROW_NUMBER() OVER(ORDER BY ");
                sbSql.AppendFormat("ch.{0} ", string.IsNullOrEmpty(_TblThanhVienRoleEntity.SortExpression) ? "[ID]" : _TblThanhVienRoleEntity.SortExpression);
                sbSql.Append(string.IsNullOrEmpty(_TblThanhVienRoleEntity.SortDirection) ? " DESC" : _TblThanhVienRoleEntity.SortDirection);
                sbSql.Append(") AS RowNum,").Append("\n");
                sbSql.Append("  ch.[Id],").Append("\n");
                sbSql.Append("  ch.[SiteMapID],").Append("\n");
                sbSql.Append("  ch.[ThanhVienID],").Append("\n");
                sbSql.Append("  ch.[RoleID]").Append("\n");
                sbSql.Append("  FROM").Append("\n");
                sbSql.Append("  tblThanhVienRole ch").Append("\n");
                sbSql.Append("  WHERE").Append("\n");
                sbSql.Append("  1 = 1").Append("\n");

                if (!string.IsNullOrEmpty(_TblThanhVienRoleEntity.SiteMapId.ToString()))
                {
                    _SqlParameter[0] = new SqlParameter("@SiteMapId", _TblThanhVienRoleEntity.SiteMapId);
                    sbSql.Append("  AND ch.[SiteMapID] LIKE @SiteMapId").Append("\n");

                }

                if (!string.IsNullOrEmpty(_TblThanhVienRoleEntity.ThanhVienId.ToString()))
                {
                    _SqlParameter[2] = new SqlParameter("@ThanhVienId", _TblThanhVienRoleEntity.ThanhVienId);
                    sbSql.Append("  AND ch.[ThanhVienID] LIKE @ThanhVienId").Append("\n");
                }

                if (!string.IsNullOrEmpty(_TblThanhVienRoleEntity.RoleId.ToString()))
                {
                    _SqlParameter[2] = new SqlParameter("@RoleId", _TblThanhVienRoleEntity.RoleId);
                    sbSql.Append("  AND ch.[RoleID] LIKE @RoleId").Append("\n");
                }
                sbSql.Append(")").Append("\n");
                sbSql.Append("SELECT *, (SELECT Max(RowNum) FROM tblTemp) TotalItems FROM tblTemp").Append("\n");
                sbSql.AppendFormat("WHERE RowNum BETWEEN {0} AND {1}", _TblThanhVienRoleEntity.StartIndex, _TblThanhVienRoleEntity.EndIndex).Append("\n");
            }
            catch { }
            return AE.SqlHelper.ExecuteDataTable(Conn, CommandType.Text, sbSql.ToString(), _SqlParameter);
        }

        public int DeleteThanhVienRole(TblThanhVienRoleEntity _TblThanhVienRoleEntity)
        {
            System.Text.StringBuilder sbSql = new System.Text.StringBuilder();
            SqlParameter[] _SqlParameter = new SqlParameter[3];
            try
            {
                sbSql.AppendFormat("DELETE FROM tblThanhVienRole").Append("\n");
                sbSql.Append("WHERE").Append("\n");
                sbSql.Append("  1=1 ").Append("\n");

                if (!string.IsNullOrEmpty(_TblThanhVienRoleEntity.SiteMapId.ToString()))
                {
                    _SqlParameter[0] = new SqlParameter("@SiteMapId", _TblThanhVienRoleEntity.SiteMapId);
                    sbSql.Append("  AND [SiteMapID] LIKE @SiteMapId").Append("\n");

                }

                if (!string.IsNullOrEmpty(_TblThanhVienRoleEntity.ThanhVienId.ToString()))
                {
                    _SqlParameter[2] = new SqlParameter("@ThanhVienId", _TblThanhVienRoleEntity.ThanhVienId);
                    sbSql.Append("  AND [ThanhVienID] LIKE @ThanhVienId").Append("\n");
                }

                if (!string.IsNullOrEmpty(_TblThanhVienRoleEntity.RoleId.ToString()))
                {
                    _SqlParameter[2] = new SqlParameter("@RoleId", _TblThanhVienRoleEntity.RoleId);
                    sbSql.Append("  AND [RoleID] LIKE @RoleId").Append("\n");
                }
            }
            catch { }
            return AE.SqlHelper.ExecuteNonQuery(Conn, CommandType.Text, sbSql.ToString(), _SqlParameter);
        }  
	}
}
